package com.global.dao.jdbc.impl;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;

import com.global.base.ExceptionUtil;
import com.global.base.StringUtil;
import com.global.dao.jdbc.GenericJdbcDao;
import com.global.vo.DBColumn;
import com.global.vo.JavaDataType;
import com.global.vo.QueryParameter;

/**
 * 
 * @author zly601436
 *
 */
public class GenericJdbcDaoImpl extends NamedParameterJdbcDaoSupport implements GenericJdbcDao {

	private final Log log = LogFactory.getLog(getClass());
	
	private String showSql;

	private List<String> errorMessageList = new ArrayList<String>();

	/**
     * {@inheritDoc}
     */
	public String getErrorMessage() {
		return StringUtil.getString(errorMessageList, ",");
	}

	/**
     * {@inheritDoc}
     */
	public ResultSet getResultSet(String staticSqlStr) throws CannotGetJdbcConnectionException, SQLException {
		
		Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		
		if(Boolean.getBoolean(showSql)){
			log.debug(staticSqlStr);
		}
		
		return statement.executeQuery(staticSqlStr);
	}

	/**
     * {@inheritDoc}
     */
	public List<Map<String, Object>> queryForList(String staticSql){
		if(StringUtils.isNotBlank(staticSql)){
			return getJdbcTemplate().queryForList(staticSql);
		}
		
		return new ArrayList<Map<String, Object>>();
	}
	
	/**
     * {@inheritDoc}
     */
	public List<Map<String, Object>> queryForList(String namedParameterSql, List<QueryParameter> queryParameters){
		if(StringUtils.isNotBlank(namedParameterSql) && queryParameters!=null){
			MapSqlParameterSource paramMap = new MapSqlParameterSource();
			
			for(QueryParameter queryParameter : queryParameters){
				paramMap.addValue(queryParameter.getName(), queryParameter.getValue());
			}
			
			RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
				public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
					Map<String, Object> map = new HashMap<String, Object>();
					
					for(int columnIndex=1; columnIndex<rs.getMetaData().getColumnCount(); columnIndex++){
						String columnName = rs.getMetaData().getColumnName(columnIndex);
						map.put(columnName, rs.getObject(columnIndex));
					}

					return map;
				}};
				
			return getNamedParameterJdbcTemplate().query(namedParameterSql, paramMap, mapper);
		}
		
		return new ArrayList<Map<String, Object>>();
	}
	
	/**
     * {@inheritDoc}
     */
	public ResultSet getResultSet(String namedParameterSql, List<QueryParameter> queryParameters) throws CannotGetJdbcConnectionException, SQLException {
		if(StringUtils.isNotBlank(namedParameterSql)){
			//大数据量调用(可滚动的结果集) 
			PreparedStatement statement = getConnection().prepareStatement(namedParameterSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (queryParameters != null) {
				for (int i = 0; i < queryParameters.size(); i++) {					
					QueryParameter queryParameter = queryParameters.get(i);					
					int parameterIndex = i+1;
					statement.setObject(parameterIndex, queryParameter.getValue());
				}
				
				return statement.executeQuery();
			}else{
				errorMessageList.add("对于带命名参数的SQL【" + namedParameterSql + "】没有参数信息，则无法执行");
			}
		}else{
			errorMessageList.add("对于带命名参数的SQL为空，则无法执行");
		}
		
		return null;
	}
	
    /**
     * {@inheritDoc}
     */
	public ResultSetMetaData getMetaData(ResultSet resultSet) {
		errorMessageList.clear();
		
		try {
			return (resultSet==null) ? null : resultSet.getMetaData();
		} catch (SQLException e) {
			errorMessageList.add(ExceptionUtil.getString(e));
		}
		
		return null;
	}
	
    /**
     * {@inheritDoc}
     */
	public Date getDbCurrentDate() {
		String sqlStr = "select sysdate from dual";		
		if(Boolean.getBoolean(showSql)){
			log.debug(sqlStr);
		}
		
		return getJdbcTemplate().queryForObject(sqlStr, Date.class);
	}
	
    /**
     * {@inheritDoc}
     */
	public DatabaseMetaData getDatabaseMetaData() {
		errorMessageList.clear();
		
		try {
			return getConnection().getMetaData();
		} catch (CannotGetJdbcConnectionException e) {
			errorMessageList.add(ExceptionUtil.getString(e));
		} catch (SQLException e) {
			errorMessageList.add(ExceptionUtil.getString(e));
		}
		
		return null;
	}

    /**
     * {@inheritDoc}
     */
	public List<DBColumn> getSQLMetaData(ResultSet resultSet) {
		errorMessageList.clear();
		
		if(resultSet!=null){
			try {
				ResultSetMetaData rs = getMetaData(resultSet);
				int columnCount = rs.getColumnCount();
					
				ArrayList<DBColumn> listColumn = new ArrayList<DBColumn>(columnCount);
				for(int i=1; i<=rs.getColumnCount(); i++){
					DBColumn c = new DBColumn();
					
					c.setAlias(rs.getColumnLabel(i));
					c.setDisplayIndex(i);
					c.setColumnTypeName(rs.getColumnTypeName(i));
					c.setDescription(rs.getColumnLabel(i));
					
					listColumn.add(c);
				}
				
				return listColumn;
			} catch (SQLException e) {
				errorMessageList.add(ExceptionUtil.getString(e));
			}
		}
		
		return new ArrayList<DBColumn>();
	}
	
    /**
     * {@inheritDoc}
     */
	public List<DBColumn> getStaticSQLMetaData(final String staticSqlStr) {
		errorMessageList.clear();
		
		if(StringUtils.isNotBlank(staticSqlStr)){
			try {
				ResultSetMetaData rs = getMetaData(getResultSet(staticSqlStr));
				int columnCount = rs.getColumnCount();
					
				ArrayList<DBColumn> listColumn = new ArrayList<DBColumn>(columnCount);
				for(int i=1; i<=rs.getColumnCount(); i++){
					DBColumn c = new DBColumn();
					
					c.setAlias(rs.getColumnLabel(i));
					c.setDisplayIndex(i);
					c.setColumnTypeName(rs.getColumnTypeName(i));
					c.setDescription(rs.getColumnLabel(i));
					
					listColumn.add(c);
				}
				
				return listColumn;
			} catch (Exception e) {
				errorMessageList.add(ExceptionUtil.getString(e));
			}
		}
		
		return new ArrayList<DBColumn>();
	}

    /**
     * {@inheritDoc}
     */
	public List<DBColumn> getNamedParameterSQLMetaData(final String namedParameterSqlStr, List<QueryParameter> namedParameters) {
		errorMessageList.clear();
		
		try {
			ResultSetMetaData rs = getMetaData(getResultSet(namedParameterSqlStr, namedParameters));
			if(rs!=null){
				int columnCount = rs.getColumnCount();
				
				ArrayList<DBColumn> listColumn = new ArrayList<DBColumn>(columnCount);
				for(int i=1; i<=rs.getColumnCount(); i++){
					DBColumn c = new DBColumn();
					
					String alias = rs.getColumnLabel(i);
					
					c.setAlias(alias);
					c.setDisplayIndex(i);
					c.setColumnTypeName(rs.getColumnTypeName(i));
					c.setDescription(alias);
					
					listColumn.add(c);
					
					if(StringUtils.containsAny(alias, "()".toCharArray())){
						errorMessageList.add("第【" + i + "】个字段没有设置别名，则不能作为过滤参数");
					}
				}
				
				return listColumn;
			}
		} catch (CannotGetJdbcConnectionException e) {
			errorMessageList.add(ExceptionUtil.getString(e));
		} catch (SQLException e) {
			errorMessageList.add(ExceptionUtil.getString(e));
		}
		
		return new ArrayList<DBColumn>();
	}
	
	
//	public boolean get(Class modelClass){
//		Table table = AnnotationUtils.findAnnotation(modelClass, Table.class);
//		if(){
//			
//		}
//        int cnt = getJdbcTemplate().queryForInt("select 1 from " + table.name() + " where rownum == 1");
//        
//
//        return cnt>0 ? true : false;
//	}
	
	private int getSqlType(String javaDataType){
		
		ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement("");
		parsedSql.toString();
		
		if(javaDataType.equalsIgnoreCase(JavaDataType.DATE)){
			return java.sql.Types.DATE;			
		}if(javaDataType.equalsIgnoreCase(JavaDataType.DOUBLE)){
			return java.sql.Types.DOUBLE;			
		}if(javaDataType.equalsIgnoreCase(JavaDataType.STRING)){
			return java.sql.Types.VARCHAR;			
		}if(javaDataType.equalsIgnoreCase(JavaDataType.LONG)){
			return java.sql.Types.NUMERIC;		
		}if(javaDataType.equalsIgnoreCase(JavaDataType.TIMESTAMP)){
			return java.sql.Types.TIMESTAMP;		
		}else{
			return -10000;
		}
	}
}

